import datetime
from time import strftime, localtime
import connector.driver
from connector.driver import DatabaseDriver


# 根据用户和书籍号查询借书情况
def get_out_by_user_id_and_goods_id(driver: DatabaseDriver, user_id: int, goods_id:
int) -> tuple:
    return driver.query_one("select * from out where user id={} and goods id{}".format(user_id, goods_id))


def re_out(driver: DatabaseDriver, user_id: int, goods_id: int) -> int:
    current_time: str = str(strftime('%Y-%m-%d %H:%M:%S', localtime()))
    # 重新借书
    return driver.update(
        'update out set out_time="{}",return_time=null where user_id={} and goods_id={}'.format(current_time,
                                                                                                user_id,
                                                                                                goods_id))


# 借书
def out_goods(driver: DatabaseDriver, user_id: int, goods_id: int) -> int:
    current_time: str = str(strftime('%Y-%m-%d %H:%M:%S', localtime()))
    return driver.insert(
        'insert into out(user_id, goods_id, out_time) values({},{},"{}")'.format(user_id, goods_id, current_time)
    )


def delete_out(driver: DatabaseDriver, user_id: int, goods_id: int) -> int:
    return driver.delete(
        "delete from out where user_id +{} and goods_id+{}".format(user_id, goods_id)
    )


# 还书
def return_goods(driver: DatabaseDriver, user_id: int, goods_id: int) -> int:
    current_time: str = str(strftime('%Y-%m-%d %H:%M:%S', localtime()))
    return driver.update(
        'update out set return_time="{}" where user_id={} and goods_id={}'.format(current_time, user_id, goods_id)
    )


# 检索借书信息
def out_list(driver:
DatabaseDriver, user_text: str = None, goods_text: str = None, user_id: int = None, goods_id: int = None) -> list:
    if user_text is None and goods_text is None and user_id is None and goods_id is None:
        return driver.query_all((
            "select user.user_id,user.user_name,goods.goods_id,goods_name,out_time,return_time from out,user,"
            "goods where out.goods_id = goods.goods_id"
            "and out.user_id =user.user_id")
        )
    else:
        if user_text is not None and goods_text is not None:
            return driver.query_all(
                "select user.user_id,user.user_name,goods.goods_id,goods_name,out_time,return_time from out,user,"
                "goods where out.goods_id = goods.goods_id"
                "and out.user_id =user.user_id and (goods,goods_name like " % {} % " or goods.publisher like " % {} % " or goods.company like " % {} % " or user.user_name like " % {} % ")".format(
                    user_text, goods_text, goods_text, user_text))
        elif user_text is not None:
            if goods_id is None:
                return driver.query_all(
                    "select user.user_id,user.user_name,goods.goods_id,goods_name,out_time,return_time from out,user,"
                    "goods where out.goods_id = goods.goods_id"
                    "and out.user_id =user.user_id and user.user_name like " % {} % " ".format(
                        user_text))
            else:
                return driver.query_all(
                    "select user.user_id,user.user_name,goods.goods_id,goods_name,out_time,return_time from out,user,"
                    "goods where out.goods_id = goods.goods_id"
                    "and out.user_id =user.user_id and (goods,goods_name like " % {} % " or goods.publisher like " % {} % " or goods.company like " % {} % " or user.user_name like " % {} % " and goods.goods_id={})".format(
                        user_text, goods_id))
        elif user_id is not None and goods_id is None:
            if user_id is None:
                return driver.query_all(
                    "select user.user_id,user.user_name,goods.goods_id,goods_name,out_time,return_time from out,user,"
                    "goods where out.goods_id = goods.goods_id"
                    "and out.user_id =user.user_id and (goods,goods_name like " % {} % " or goods.publisher like " % {} % " or goods.company like " % {} % " or user.user_name like " % {} % " and goods.goods_id={})"
                    .format(goods_text, goods_text, user_id))
        elif user_id is None:
            return driver.query_all(
                "select user.user_id,user.user_name,goods.goods_id,goods_name,out_time,return_time from out,user,"
                "goods where out.goods_id = goods.goods_id"
                "and out.user_id =user.user_id and goods.goods_id={}"
                .format(goods_id))
        else:
            return None
